''' 设备管理 '''
import sys,traceback,logging,datetime
import time

import pymysql
from dbutils.pooled_db import PooledDB

from faceRecogn.faceRecognView import broadcast_msg
from utils.edit_device import addUser, delUser
from utils.db_config import mysqlInfo
from utils.process_image import url_encode_base64


import math



logger = logging.getLogger('app')

# 创建数据库连接
def connect_db():
    pool=PooledDB(creator=pymysql,mincached=2, maxcached=5,maxshared=3, maxconnections=6, blocking=True,host=mysqlInfo['host'],port=mysqlInfo['port'],
        user=mysqlInfo['user'],password=mysqlInfo['password'],database=mysqlInfo['database'],charset=mysqlInfo['charset'],cursorclass=pymysql.cursors.DictCursor)
    return pool.connection()

'''
设备管理页面组 start
'''
'''设备组页面 start'''
#获取设备组信息数据
def equipment_group(params: dict[str, str]):
    conn = connect_db()
    cur = conn.cursor()
    pageCurrent = int(params['page'])
    pageSize = int(params['pageSize'])
    searchValue = params.get('searchValue', '')  # 不一定存在的参数
    projectId = params.get('selectProject','') #不一定存在的参数
    projcet_list = []
    result = {}
    try:
        # 构建长字符串常用的方式，这不是元组！
        base_sql = (r"SELECT equipment_group.id AS equipmentGroupId, equipment_group.name AS equipmentGroupName, remark, projects.name AS projectName "
                    r"FROM equipment_group JOIN projects ON equipment_group.project_id=projects.id")
        # 筛选条件
        sql_conditions = []
        sql_params = []
        # 动态添加sql命令
        if searchValue:
            sql_conditions.append("projects.name LIKE %s")
            sql_params.append(f"%{searchValue}%")  # 进行模糊匹配，%是LIKE子句的一部分
        if projectId:
            sql_conditions.append("projects.id = %s")
            sql_params.append(projectId)
        # 完整的SQL查询
        if sql_conditions:
            sql = f"{base_sql} WHERE {' AND '.join(sql_conditions)}"  # ' AND '.join(conditions) .join是python中的字符串方法，将可迭代对象中的所有元素连接成一个单一的字符串，调用其自身的字符串作为分隔符
            cur.execute(sql, sql_params)
        else:
            cur.execute(base_sql)

        fetchall = cur.fetchall()
        itemCount = len(fetchall)
        pageCount = math.ceil(itemCount / pageSize)
        start_idx = (pageCurrent - 1) * pageSize
        end_idx = start_idx + pageSize
        pageData = fetchall[start_idx:min(end_idx, itemCount)]
        for row in pageData:
            row_dict = {}
            row_dict['equipmentGroupId'] = row['equipmentGroupId']
            row_dict['equipmentGroupName'] = row['equipmentGroupName']
            row_dict['projectName'] = row['projectName']
            row_dict['remark'] = row['remark']
            projcet_list.append(row_dict)

        resultData = {
            'page': pageCurrent,
            'pageSize': pageSize,
            'pageCount': pageCount,
            'itemCount': itemCount,
            'list': projcet_list
        }
        result['code'] = 200
        result['result'] = resultData
        result['message'] = 'ok'
        conn.commit()
    except:
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        # print(result)
        return result

#删除equipment_group信息数据
def del_equipment_group(params: dict[str: list]):
    conn = connect_db()
    cur = conn.cursor()
    equipmentGroupList = params.get('equipmentGroupList', '')
    result = {}

    try:
        sql = (f"SELECT equipment_group.name "
               f"FROM equipment_group JOIN equipment ON equipment_group.id = equipment.equipment_group_id "
               f"WHERE equipment.equipment_group_id in (%s)")
        cur.execute(sql,equipmentGroupList)
        fetchone = cur.fetchone()
        # print(fetchone)
        if fetchone:
            result['result'] = fetchone['name']
            result['code'] = 0
            result['message'] = f"删除失败：请先删除设备组<--{fetchone['name']}-->管理的所有设备"
            return result
        placeholders = ', '.join(['%s']*len(equipmentGroupList))
        sql = (f"DELETE FROM equipment_group WHERE id in ({placeholders})")
        cur.execute(sql, equipmentGroupList)
        result['result'] = 'ok'
        result['code'] = 200
        result['message'] = '成功删除'
        conn.commit()  # 提交事务
    except:
        result['result'] = 'ng'
        result['code'] = 0
        result['message'] = '删除失败，请联系管理人员'
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result

#添加设备组
def add_equipmentGroup(params: dict[str, str]):
    conn = connect_db()
    cur = conn.cursor()
    # 定义前端字段名到数据库字段名的映射
    field_mapping = {
        'equipmentGroup': 'name',
        'project': 'project_id',
        'remark': 'remark',
    }
    name = params.get('equipmentGroup')
    project_id = params.get('project')
    result = {}
    try:
        sql = ("SELECT * FROM equipment_group WHERE name = %s AND project_id = %s")
        cur.execute(sql, [name, project_id])
        if len(cur.fetchall()):
            result['result'] = 'ng'
            result['code'] = 0
            result['message'] = f'设备组名重复：<{name}>设备组已经在所选项目中'
            return result

        insert_fields = []
        placeholders = []
        insert_values = []
        for key, value in params.items():
            if key != 'equipmentGroupId':
                # print(value)
                insert_fields.append(field_mapping[key])
                placeholders.append('%s')
                insert_values.append(value)
        sql = f"INSERT INTO equipment_group({', '.join(insert_fields)}) VALUES({', '.join(placeholders)})"
        # print(sql)
        cur.execute(sql, insert_values)

        result['code'] = 200
        result['message'] = '新增设备组成功'
        result['result'] = 'ok'
        conn.commit()
    except :
        result['code'] = 0
        result['message'] = '新增失败，请联系管理员'
        result['result'] = 'ng'
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result

#equipmentGroup编辑页面初始化数据
def init_equipmentGroup(params: dict[str, str]):
    conn = connect_db()
    cur = conn.cursor()
    id = params['equipmentGroupId']
    result = {}
    # 返回给前端的字典
    try:
        sql = (f"SELECT equipment_group.name AS equipmentGroupName, equipment_group.id AS equipmentGroupId, remark, projects.name AS projectName, projects.id AS projectId "
               f"FROM equipment_group JOIN projects ON equipment_group.project_id = projects.id "
               f"WHERE equipment_group.id = %s")
        cur.execute(sql,[id])
        row = cur.fetchone()
        projectInfo = {}
        projectInfo['equipmentGroup'] = row['equipmentGroupName']
        projectInfo['equipmentGroupId'] = row['equipmentGroupId']
        projectInfo['project'] = row['projectName']
        projectInfo['remark'] = row['remark']
        projectInfo['projectId'] = row['projectId']
        result['result'] = projectInfo
        result['code'] = 200
        result['message'] = 'ok'
    except:
        result['result'] = 'ng'
        result['code'] = 0
        result['message'] = '编辑页面初始化失败'
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result

#更新equipmentGroup信息数据
def update_equipmentGroup(params: dict[str, str]):
    conn = connect_db()
    cur = conn.cursor()
    # 定义前端字段名到数据库字段名的映射
    field_mapping = {
        'equipmentGroupId': 'id',
        'equipmentGroup': 'name',
        'project': 'project_id',
        'remark': 'remark',
    }
    result = {}
    # print(params)
    try:
        # 前端返回的可能是项目id，也可能是项目名，在这儿统一为项目名
        project = params.get('project')
        sql = ("SELECT id FROM projects WHERE %s in (id,name)")
        cur.execute(sql,project)
        # print(f"参数是：{params}")
        params['project'] = cur.fetchone()['id']
        # print(f"参数是：{params}")
        sql = ("SELECT * FROM equipment_group WHERE name = %s AND id != %s AND project_id = %s")
        # print(sql)
        cur.execute(sql, [params['equipmentGroup'], params['equipmentGroupId'], params['project']])
        if len(cur.fetchall()):
            result['result'] = 'ng'
            result['code'] = 0
            result['message'] = '设备组名重复：设备组名在所选项目中重复'
            return result
        update_fields = []
        update_values = []
        for key, value in params.items():
            update_fields.append(f"{field_mapping[key]} = %s")
            update_values.append(value)
        sql = f"UPDATE equipment_group SET {', '.join(update_fields)} WHERE id = %s"
        # print(f"初始sql:{sql}",update_values)
        update_values.append(params['equipmentGroupId'])
        cur.execute(sql,update_values)
        result['result'] = 'ok'
        result['code'] = 200
        result['message'] = 'ok'
        conn.commit()
    except :
        result['result'] = 'ng'
        result['code'] = 0
        result['message'] = '更新信息失败'
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result

'''设备组页面 end'''

'''设备列表页面 start'''
#获取设备列表信息数据
def equipment_list(params: dict[str, str]):
    conn = connect_db()
    cur = conn.cursor()
    pageCurrent = int(params['page'])
    pageSize = int(params['pageSize'])
    searchValue = params.get('searchValue', '')  # 不一定存在的参数
    projectId = params.get('selectProject','') #不一定存在的参数
    groupId = params.get('selectGroup', '')  # 不一定存在的参数
    # type的label-value转换，和前端保持一致
    equipmentType = {
        '0': '入口',
        '1': '出口',
    }
    equipment_list = []
    result = {}
    try:
        # 构建长字符串常用的方式，这不是元组！
        base_sql = (r"SELECT equipment.id AS equipmentId, equipment.name AS equipmentName, is_online, "
                    "equipment.type AS equipmentType, equipment.create_date AS date, "
                    "equipment_group.name AS equipmentGroup, projects.name AS projectName "
                    "FROM equipment "
                    "JOIN equipment_group ON equipment.equipment_group_id = equipment_group.id "
                    "JOIN projects ON equipment.project_id = projects.id")
        # 筛选条件
        sql_conditions = []
        sql_params = []
        # 动态添加sql命令
        if searchValue:
            sql_conditions.append("equipment.name LIKE %s")
            sql_params.append(f"%{searchValue}%")  # 进行模糊匹配，%是LIKE子句的一部分
        if projectId:
            sql_conditions.append("projects.id = %s")
            sql_params.append(projectId)
        if groupId:
            sql_conditions.append("equipment_group.id = %s")
            sql_params.append(groupId)
        # 完整的SQL查询
        if sql_conditions:
            sql = f"{base_sql} WHERE {' AND '.join(sql_conditions)}"  # ' AND '.join(conditions) .join是python中的字符串方法，将可迭代对象中的所有元素连接成一个单一的字符串，调用其自身的字符串作为分隔符
            # print(sql)
            cur.execute(sql, sql_params)
        else:
            # print(base_sql)
            cur.execute(base_sql)
        fetchall = cur.fetchall()
        itemCount = len(fetchall)
        pageCount = math.ceil(itemCount / pageSize)
        start_idx = (pageCurrent - 1) * pageSize
        end_idx = start_idx + pageSize
        pageData = fetchall[start_idx:min(end_idx, itemCount)]
        for row in pageData:
            row_dict = {}
            row_dict['equipmentId'] = row['equipmentId']
            row_dict['equipmentName'] = row['equipmentName']
            row_dict['isOnline'] = row['is_online']
            row_dict['equipmentGroup'] = row['equipmentGroup']
            row_dict['projectName'] = row['projectName']
            row_dict['equipmentType'] = equipmentType[str(row['equipmentType'])] #type的lable-value转换
            row_dict['date'] = row['date'].strftime('%Y-%m-%d')
            equipment_list.append(row_dict)

        resultData = {
            'page': pageCurrent,
            'pageSize': pageSize,
            'pageCount': pageCount,
            'itemCount': itemCount,
            'list': equipment_list
        }
        result['code'] = 200
        result['result'] = resultData
        result['message'] = 'ok'
        conn.commit()
    except:
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        # print(result)
        return result

#获取选择班组下拉框选项
def equipment_groupOptions(selectProjectId: int):
    conn = connect_db()
    cur = conn.cursor()
    result = {}
    groupOptions = []
    try:
        sql = ("SELECT equipment_group.name AS groupName, equipment_group.id AS groupId "
               "FROM equipment_group "
               "JOIN projects ON equipment_group.project_id = projects.id "
               "WHERE projects.id = %s")
        cur.execute(sql, [selectProjectId])
        for row in cur.fetchall():
            projcetOption_dict = {}
            projcetOption_dict["label"] = row['groupName']
            projcetOption_dict["value"] = str(row['groupId'])
            groupOptions.append(projcetOption_dict)
        result['result'] = groupOptions
        result['code'] = 200
        result['message'] = 'ok'
    except:
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result
# get_groupOptions(1)

# 删除设备
def del_equipment(params: dict[str: list]):
    conn = connect_db()
    cur = conn.cursor()
    delEquipmentList = params.get('delEquipmentList', '')
    result = {}

    try:
        placeholders = ', '.join(['%s']*len(delEquipmentList))
        sql = (f"DELETE FROM equipment WHERE id in ({placeholders})")
        # print(sql,delEquipmentList)
        cur.execute(sql, delEquipmentList)
        result['result'] = 'ok'
        result['code'] = 200
        result['message'] = '成功删除'
        conn.commit()  # 提交事务
    except:
        result['result'] = 'ng'
        result['code'] = 0
        result['message'] = '删除失败，请联系管理人员'
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result

#添加设备
def add_equipment(params: dict[str, str]):
    conn = connect_db()
    cur = conn.cursor()
    # 定义前端字段名到数据库字段名的映射
    field_mapping = {
        'equipmentId': 'id',
        'equipmentName': 'name',
        'project': 'project_id',
        'equipmentGroup': 'equipment_group_id',
        'type': 'type',
    }
    # print(params)
    equipmentId = params.get('equipmentId')
    equipmentName = params.get('equipmentName')
    equipmentGroup = params.get('equipmentGroup')
    result = {}
    try:
        # 检查设备号是否已经存在
        sql = ("SELECT * FROM equipment WHERE id = %s")
        cur.execute(sql, [equipmentId])
        if len(cur.fetchall()):
            result['result'] = 'ng'
            result['code'] = 0
            result['message'] = f'设备号重复：<{equipmentId}>设备号已经存在，请删除原设备后再添加'
            return result
        # 检查设备名是否重复，同一个设备组下面，设备名应该是唯一的
        sql = ("SELECT * FROM equipment WHERE equipment_group_id = %s AND name = %s")
        cur.execute(sql,[equipmentGroup,equipmentName])
        if len(cur.fetchall()):
            result['result'] = 'ng'
            result['code'] = 0
            result['message'] = f'设备名重复：<{equipmentName}>设备名已经在所选设备组中存在'
            return result
        # 插入新的数据
        insert_fields = []
        placeholders = []
        insert_values = []
        for key, value in params.items():
            if key != 'equipmentGroupId':
                # print(value)
                insert_fields.append(field_mapping[key])
                placeholders.append('%s')
                insert_values.append(value)
        insert_fields.append('create_date')
        placeholders.append('%s')
        insert_values.append(datetime.datetime.now())
        sql = f"INSERT INTO equipment({', '.join(insert_fields)}) VALUES({', '.join(placeholders)})"
        # print(sql)
        cur.execute(sql, insert_values)

        result['code'] = 200
        result['message'] = '新增设备成功'
        result['result'] = 'ok'
        conn.commit()
    except :
        result['code'] = 0
        result['message'] = '新增失败，请联系管理员'
        result['result'] = 'ng'
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result


#equipmentList编辑页面初始化数据
def init_equipment(params: dict[str, str]):
    conn = connect_db()
    cur = conn.cursor()
    id = params['equipmentId']
    result = {}
    # print(id)
    # 返回给前端的字典
    try:
        sql = (f"SELECT equipment.name AS equipmentName, equipment.id AS equipmentId, equipment.type AS type, equipment_group.name AS equipmentGroup, projects.name AS projectName "
               f"FROM equipment "
               f"JOIN projects ON equipment.project_id = projects.id "
               f"JOIN equipment_group ON equipment.equipment_group_id = equipment_group.id "
               f"WHERE equipment.id = %s")
        cur.execute(sql,[id])
        row = cur.fetchone()
        projectInfo = {}
        projectInfo['equipmentName'] = row['equipmentName']
        projectInfo['equipmentId'] = str(row['equipmentId'])
        projectInfo['project'] = row['projectName']
        projectInfo['equipmentGroup'] = row['equipmentGroup']
        projectInfo['type'] = str(row['type'])
        result['result'] = projectInfo
        result['code'] = 200
        result['message'] = 'ok'
    except:
        result['result'] = 'ng'
        result['code'] = 0
        result['message'] = '编辑页面初始化失败'
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result

#更新equipment信息数据
def update_equipment(params: dict[str, str]):
    conn = connect_db()
    cur = conn.cursor()
    # 定义前端字段名到数据库字段名的映射
    field_mapping = {
        'equipmentId': 'id',
        'equipmentName': 'name',
        'project': 'project_id',
        'equipmentGroup': 'equipment_group_id',
        "type": "type",
    }
    result = {}
    # print(params)
    try:
        # 前端返回的可能是项目id，也可能是项目名，在这儿统一为项目id(设备组也是一样的)
        sql = ("SELECT p.projectId, g.groupId "
               "FROM (SELECT id AS projectId FROM projects WHERE %s in (id,name)) AS p "
               "CROSS JOIN "
               "(SELECT id AS groupId FROM equipment_group WHERE %s in (id,name)) AS g")
        cur.execute(sql,[params['project'], params['equipmentGroup']])
        fetchone = cur.fetchone()
        params['project'] = fetchone['projectId']
        params['equipmentGroup'] = fetchone['groupId']

        # 判断设备名在同一个设备组下是否重复
        sql = ("SELECT * FROM equipment WHERE name = %s AND id != %s AND equipment_group_id = %s")
        # print(sql)
        cur.execute(sql, [params['equipmentName'], params['equipmentId'], params['equipmentGroup']])
        if len(cur.fetchall()):
            result['result'] = 'ng'
            result['code'] = 0
            result['message'] = '设备名重复：设备名在所选设备组中重复'
            return result
        update_fields = []
        update_values = []
        for key, value in params.items():
            update_fields.append(f"{field_mapping[key]} = %s")
            update_values.append(value)
        sql = f"UPDATE equipment SET {', '.join(update_fields)} WHERE id = %s"
        # print(f"初始sql:{sql}",update_values)
        update_values.append(params['equipmentId'])
        cur.execute(sql,update_values)
        result['result'] = 'ok'
        result['code'] = 200
        result['message'] = 'ok'
        conn.commit()
    except :
        result['result'] = 'ng'
        result['code'] = 0
        result['message'] = '更新信息失败'
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result

#获取人员信息数据
def getemployeelist(param):
    conn = connect_db()
    cur = conn.cursor()
    # print(param)
    pageSize = param['pageSize']
    pageCurrent = param['pageCurrent']
    searchValue = param['searchValue']
    selectProjectId = param['selectProjectId']
    selectGroupId = param['selectGroupId']
    result = {}
    emploee_list = []
    try:
        #构建长字符串常用的方式，这不是元组！
        base_sql = ("select e.id AS userId,e.gender AS gender,e.name AS userName,p.name AS projectName,g.group_name AS groupName, e.photo AS photo "
                    "from employee e left outer join projects p on e.project_id = p.id left outer join project_groups g on e.group_id = g.group_id "
        )
        # 筛选条件
        sql_conditions = []
        sql_params = []
        # 动态添加sql命令
        if searchValue:
            sql_conditions.append("e.name LIKE %s")
            sql_params.append(f"%{searchValue}%") # 进行模糊匹配，%是LIKE子句的一部分
        if selectProjectId:
            sql_conditions.append("p.id = %s")
            sql_params.append(selectProjectId)
        if selectGroupId:
            sql_conditions.append("g.group_id = %s")
            sql_params.append(selectGroupId)
        # 完整的SQL查询
        if sql_conditions:
            sql = f"{base_sql} WHERE {' AND '.join(sql_conditions)}" #' AND '.join(conditions) .join是python中的字符串方法，将可迭代对象中的所有元素连接成一个单一的字符串，调用其自身的字符串作为分隔符
            cur.execute(sql, sql_params)
        else:
            cur.execute(base_sql)

        fetchall = cur.fetchall()
        itemCount = len(fetchall)
        pageCount = math.ceil(itemCount / pageSize)
        start_idx = (pageCurrent - 1) * pageSize
        end_idx = start_idx + pageSize
        pageData = fetchall[start_idx:min(end_idx, itemCount)]
        for row in pageData:
            row_dict = {}
            row_dict['userId'] = row['userId']
            row_dict['userName'] = row['userName']
            row_dict['projectName'] = row['projectName']
            row_dict['groupName'] = row['groupName']
            row_dict['avatar'] = row['photo']
            row_dict['gender'] = row['gender']
            row_dict['status'] = ""
            row_dict['rlsTime'] = ""
            base_sql = ("select distinct r.emp_id, concat(concat(e.name ,':') , r.status) as status, DATE_FORMAT((select max(r1.rls_time) from release_equipment r1   "
                        "where r1.emp_id = r.emp_id and r1.equip_id =r.equip_id), '%Y-%m-%d %H:%i:%s') as rls_time  from release_equipment r inner join equipment e on e.id = r.equip_id where 1=1 "
            )
            equips = param['equipid'].split(',')
            equipstxt = ""
            rls_sts = ""
            for eq in equips:
                equipstxt += "'" + eq + "'" + ","
            if equipstxt != "":
                base_sql += " and r.equip_id in (" + equipstxt.strip(',') + ") "
            cur.execute(base_sql)
            for row1 in cur.fetchall():
                if row_dict['userId'] == row1['emp_id']:
                    rls_sts += row1['status'] + "；"
                    row_dict['rlsTime'] = row1['rls_time']
            row_dict['status'] = rls_sts
            emploee_list.append(row_dict)

        resultData = {
            'page': pageCurrent,
            'pageSize': pageSize,
            'pageCount': pageCount,
            'itemCount': itemCount,
            'list': emploee_list
        }
        result['code'] = 200
        result['result'] = resultData
        result['message'] = ''
        conn.commit()
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        # print(f"result of get_emploee_data: {result}")
        cur.close()
        conn.close()
        return result

#获取已下发人员列表数据
def getreleasedemplist(param):
    conn = connect_db()
    cur = conn.cursor()
    pageSize = param['pageSize']
    pageCurrent = param['pageCurrent']
    result = {}
    emploee_list = []
    try:
        base_sql = "select e.id AS userId,e.gender AS gender,e.name AS userName,p.name AS projectName,g.group_name AS groupName, e.photo AS photo,re.status,DATE_FORMAT(re.rls_time, '%Y-%m-%d %H:%i:%s') AS rls_time " \
                   + "from release_equipment re inner join employee e on re.emp_id = e.id left outer join projects p on e.project_id = p.id left outer join project_groups g on e.group_id = g.group_id " \
                   + "where re.equip_id = '" + param['equipid'] + "' AND re.status = '下发成功'"

        cur.execute(base_sql)

        fetchall = cur.fetchall()
        itemCount = len(fetchall)
        pageCount = math.ceil(itemCount / pageSize)
        start_idx = (pageCurrent - 1) * pageSize
        end_idx = start_idx + pageSize
        pageData = fetchall[start_idx:min(end_idx, itemCount)]
        for row in pageData:
            row_dict = {}
            row_dict['userId'] = row['userId']
            row_dict['userName'] = row['userName']
            row_dict['projectName'] = row['projectName']
            row_dict['groupName'] = row['groupName']
            row_dict['avatar'] = row['photo']
            row_dict['gender'] = row['gender']
            row_dict['status'] = row['status']
            row_dict['rlsTime'] = row['rls_time']
            emploee_list.append(row_dict)

        resultData = {
            'page': pageCurrent,
            'pageSize': pageSize,
            'pageCount': pageCount,
            'itemCount': itemCount,
            'list': emploee_list
        }
        result['code'] = 200
        result['result'] = resultData
        result['message'] = ''
        conn.commit()
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        # print(f"result of get_emploee_data: {result}")
        cur.close()
        conn.close()
        return result

#下发至设备
def sendtoequip(params):
    conn = connect_db()
    cur = conn.cursor()
    emp_list = params['empList']
    equipList = params['equipid'].split(',')
    # 移除空字符串元素
    equipList = [item for item in equipList if item]
    result = {}
    try:
        #查询待下发人员的信息
        placeholders = ','.join(['%s'] * len(emp_list))
        sql = (f"SELECT id,name,photo FROM employee WHERE id in ({placeholders})")
        cur.execute(sql, emp_list)
        employeeInfo = cur.fetchall()
        #构造下发指令列表
        addUserList = []
        for equip in equipList:
            for employee in employeeInfo:
                msg = employee
                msg['deviceId'] = equip
                msg['photo'] = url_encode_base64(msg['photo'])
                print(employee,msg)
                addUserList.append(addUser(msg))
        for user in addUserList:
            print(user)
            broadcast_msg(user)
        time.sleep(len(addUserList)*0.6)
        result['result'] = 'ok'
        result['code'] = 200
        result['message'] = ''
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result

#删除下发人员信息
def deleteempequip(params):
    conn = connect_db()
    cur = conn.cursor()
    result = {}
    equipId = params['equipid']
    empList = params['empList']
    try:
        # 构造下发指令列表
        delEmpList = []
        for employee in empList:
            msg = {}
            msg['deviceId'] = equipId
            msg['userId'] = employee
            delEmpList.append(delUser(msg))
        for Emp in delEmpList:
            print(Emp)
            broadcast_msg(Emp)

        # sql = " delete from release_equipment where equip_id = '" + params['equipid'] + "' and emp_id in ( "
        # empid = ""
        # for emp in params['empList']:
        #     empid += str(emp) + ','
        # sql += empid.strip(',') + ") "
        # cur.execute(sql)
        time.sleep(0.5)
        result['result'] = 'ok'
        result['code'] = 200
        result['message'] = '删除成功'
        conn.commit()
    except :
        result['result'] = 'ng'
        result['code'] = 0
        result['message'] = '删除失败'
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result
    
'''设备列表页面 end'''
'''
设备管理页面组 end
'''
